package com.prayerdiary.database;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

import com.prayerdiary.model.DailyPrayer;
import com.prayerdiary.utils.Utils;

public class PrayerDiaryDBHelper extends SQLiteOpenHelper
{
	private final static int DB_VERSION = 1;

	private final static String DB_NAME = "prayerdiarydb.db";

	private final static String DB_PATH = "/data/data/com.prayerdiary.app/databases/";

	// TODO if this is used only in one place then remove it
	private final static String QAZA_DATE_TABLE = "qaza_dates";

	private final static String QAZA_PRAYER_RECORD_TABLE = "prayer_record";

	private final static String CURRENT_PRAYER_RECORD_TABLE = "current_prayer_record";

	private final static String CURRENT_PRAYER_DATES_TABLE = "current_prayer_dates";

	private SQLiteDatabase db;

	private Context context;

	public PrayerDiaryDBHelper(Context context)
	{
		super(context, DB_NAME, null, DB_VERSION);
		this.context = context;
		try
		{
			createDataBase();
		}
		catch (Exception e)
		{
			Utils.println(e.getMessage());
		}
	}

	@Override
	public void onCreate(SQLiteDatabase db)
	{
		try
		{
			String query = "CREATE TABLE IF NOT EXISTS " + QAZA_DATE_TABLE + " (" + "date long PRIMARY KEY, " + "period TEXT, " + "year INTEGER, "
					+ "month INTEGER, week INTEGER, day INTEGER);";
			db.execSQL(query);
			query = "CREATE TABLE IF NOT EXISTS  " + QAZA_PRAYER_RECORD_TABLE + " (date long PRIMARY KEY, fajr BOOLEAN DEFAULT 'FALSE'," + " zuhr BOOLEAN DEFAULT 'FALSE',"
					+ " asar BOOLEAN DEFAULT 'FALSE'," + " maghrib BOOLEAN DEFAULT 'FALSE'," + " isha BOOLEAN DEFAULT 'FALSE');";
			db.execSQL(query);

			query = "CREATE TABLE IF NOT EXISTS  " + CURRENT_PRAYER_RECORD_TABLE + " (date long PRIMARY KEY, fajr BOOLEAN DEFAULT 'FALSE'," + " zuhr BOOLEAN DEFAULT 'FALSE',"
					+ " asar BOOLEAN DEFAULT 'FALSE'," + " maghrib BOOLEAN DEFAULT 'FALSE'," + " isha BOOLEAN DEFAULT 'FALSE');";
			db.execSQL(query);

			query = "CREATE TABLE IF NOT EXISTS  " + CURRENT_PRAYER_DATES_TABLE + " (" + "date long PRIMARY KEY, " + "period TEXT, " + "year INTEGER, "
					+ "month INTEGER, week INTEGER, day INTEGER);";
			db.execSQL(query);
		}
		catch (Exception e)
		{
			Utils.println(e.getMessage());
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
	{
		onCreate(db);
	}

	public void createDataBase() throws IOException
	{
		boolean dbExist = checkDataBase();
		if (!dbExist)
		{
			this.getReadableDatabase();
			try
			{
				copyDataBase();
			}
			catch (Exception e)
			{
				throw new Error("Error copying database");
			}
		}
	}

	private boolean checkDataBase()
	{
		SQLiteDatabase checkDB = null;
		String myPath = DB_PATH + DB_NAME;
		try
		{
			checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
		}
		catch (SQLiteException e)
		{
			copyDataBase();
			Utils.println("DataBase Can not be accessed therefore new DataBase will be copied");
		}
		if (checkDB != null)
		{
			checkDB.close();
		}
		return checkDB != null ? true : false;
	}

	private boolean copyDataBase()
	{
		try
		{
			Utils.println("copyDataBase()");
			InputStream myInput = context.getAssets().open(DB_NAME);
			String outFileName = DB_PATH + DB_NAME;
			OutputStream myOutput = new FileOutputStream(outFileName);
			byte[] buffer = new byte[1024];
			int length;
			while ((length = myInput.read(buffer)) > 0)
			{
				myOutput.write(buffer, 0, length);
			}
			myOutput.flush();
			myOutput.close();
			myInput.close();
		}
		catch (FileNotFoundException e)
		{
			Utils.println("DataBase NotFound ERROR = " + e);
		}
		catch (Exception e)
		{
			Utils.println("DataBase Copy ERROR = " + e);
		}
		return true;
	}

	public void openDataBase() throws SQLException
	{
		// TODO check if the database is already open then return the previous
		// opened database
		String myPath = DB_PATH + DB_NAME;
		try
		{
			db = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
		}
		catch (Exception e)
		{
			Utils.println("Database Open ERROR = " + e);
		}
	}

	public void close()
	{
		try
		{
			if (db != null)
			{
				db.close();
				super.close();
			}
		}
		catch (Exception e)
		{
			Utils.println("Error while closing the dataBase = " + e);
		}
	}

	/**
	 * Insert the days detail into the database.
	 * <p>
	 * Insert corresponding date into qaza_prayer_record table
	 */
	public boolean addQazaDay(Date date)
	{
		boolean inserted = true;
		long longDate = date.getTime();
		Calendar c = Calendar.getInstance();
		c.setTime(date);

		String query = "INSERT INTO qaza_dates(date, period, year, month, week, day) VALUES (" + longDate + ", 'DAY'," + c.get(Calendar.YEAR) + "," + c.get(Calendar.MONTH) + ","
				+ c.get(Calendar.WEEK_OF_YEAR) + "," + c.get(Calendar.DAY_OF_MONTH) + ");";

		String prayerRecordQuery = "INSERT INTO prayer_record (date) VALUES (" + longDate + ");";

		try
		{
			openDataBase();
			db.execSQL(query);
			db.execSQL(prayerRecordQuery);
		}
		catch (Exception e)
		{
			Utils.println(e.getMessage());
			inserted = false;
		}
		finally
		{
			if (db != null) db.close();
		}
		return inserted;
	}

	public boolean addCurrentDay(Date date)
	{
		boolean inserted = true;
		long longDate = date.getTime();
		Calendar c = Calendar.getInstance();
		c.setTime(date);

		String query = "INSERT INTO current_prayer_dates(date, period, year, month, week, day) VALUES (" + longDate + ", 'DAY'," + c.get(Calendar.YEAR) + ","
				+ c.get(Calendar.MONTH) + "," + c.get(Calendar.WEEK_OF_YEAR) + "," + c.get(Calendar.DAY_OF_MONTH) + ");";

		String prayerRecordQuery = "INSERT INTO current_prayer_record (date) VALUES (" + longDate + ");";

		try
		{
			openDataBase();
			db.execSQL(query);
			Utils.println("Executing Query:" + query);
			db.execSQL(prayerRecordQuery);
			Utils.println("Executing Query:" + prayerRecordQuery);
		}
		catch (Exception e)
		{
			Utils.println(e.getMessage());
			inserted = false;
		}
		finally
		{
			if (db != null) db.close();
		}
		return inserted;
	}

	/**
	 * Retrieves all the values from qaza prayer record table
	 */
	public ArrayList<DailyPrayer> getQazaPrayerRecord()
	{
		ArrayList<DailyPrayer> list = null;
		Cursor cursor = null;
		try
		{
			String query = "select * from prayer_record ORDER BY date DESC";
			openDataBase();
			cursor = db.rawQuery(query, null); // TODO check if the
												// cursor
			// can be null?
			list = new ArrayList<DailyPrayer>(cursor.getCount());

			while (cursor.moveToNext())
			{
				DailyPrayer j = new DailyPrayer();
				j.setDate(cursor.getLong(0));
				String value = cursor.getString(1);
				if ("TRUE".equalsIgnoreCase(value))
				{
					j.setFajr(true);
				}
				value = cursor.getString(2);
				if ("TRUE".equalsIgnoreCase(value))
				{
					j.setZuhr(true);
				}
				value = cursor.getString(3);
				if ("TRUE".equalsIgnoreCase(value))
				{
					j.setAsar(true);
				}
				value = cursor.getString(4);
				if ("TRUE".equalsIgnoreCase(value))
				{
					j.setMaghrib(true);
				}
				value = cursor.getString(5);
				if ("TRUE".equalsIgnoreCase(value))
				{
					j.setIsha(true);
				}
				list.add(j);
			}
		}
		catch (Exception e)
		{
			Utils.println("getSearchResults ERROR = " + e);
		}
		finally
		{
			if (cursor != null) cursor.close();
			close();
		}
		return list;
	}

	/**
	 * Retrieves all the values from Current prayer record table
	 */
	public ArrayList<DailyPrayer> getCurrentPrayerRecord()
	{
		ArrayList<DailyPrayer> list = null;
		Cursor cursor = null;
		try
		{
			String query = "select * from current_prayer_record ORDER BY date DESC";
			openDataBase();
			cursor = db.rawQuery(query, null); // TODO check if the
												// cursor
			// can be null?
			list = new ArrayList<DailyPrayer>(cursor.getCount());

			while (cursor.moveToNext())
			{
				DailyPrayer j = new DailyPrayer();
				j.setDate(cursor.getLong(0));
				String value = cursor.getString(1);
				if ("TRUE".equalsIgnoreCase(value))
				{
					j.setFajr(true);
				}
				value = cursor.getString(2);
				if ("TRUE".equalsIgnoreCase(value))
				{
					j.setZuhr(true);
				}
				value = cursor.getString(3);
				if ("TRUE".equalsIgnoreCase(value))
				{
					j.setAsar(true);
				}
				value = cursor.getString(4);
				if ("TRUE".equalsIgnoreCase(value))
				{
					j.setMaghrib(true);
				}
				value = cursor.getString(5);
				if ("TRUE".equalsIgnoreCase(value))
				{
					j.setIsha(true);
				}
				list.add(j);
			}
		}
		catch (Exception e)
		{
			Utils.println("getSearchResults ERROR = " + e);
		}
		finally
		{
			if (cursor != null) cursor.close();
			close();
		}
		return list;
	}

	public boolean updateQazaPrayer(DailyPrayer prayer)
	{
		boolean updated = true;
		Cursor cursor = null;
		try
		{
			String query = "Update prayer_record set fajr = '" + String.valueOf(prayer.getFajr()) + "', zuhr = '" + String.valueOf(prayer.getZuhr()) + "', asar = '"
					+ String.valueOf(prayer.getAsar()) + "', maghrib = '" + String.valueOf(prayer.getMaghrib()) + "', isha = '" + String.valueOf(prayer.getIsha())
					+ "' where date = " + prayer.getDate() + ";";
			openDataBase();
			db.execSQL(query);
		}
		catch (Exception e)
		{
			Utils.println("getSearchResults ERROR = " + e);
			updated = false;
		}
		finally
		{
			if (cursor != null) cursor.close();
			close();
		}
		return updated;
	}

	public boolean updateCurrentPrayer(DailyPrayer prayer)
	{
		boolean updated = true;
		Cursor cursor = null;
		try
		{
			String query = "Update current_prayer_record set fajr = '" + String.valueOf(prayer.getFajr()) + "', zuhr = '" + String.valueOf(prayer.getZuhr()) + "', asar = '"
					+ String.valueOf(prayer.getAsar()) + "', maghrib = '" + String.valueOf(prayer.getMaghrib()) + "', isha = '" + String.valueOf(prayer.getIsha())
					+ "' where date = " + prayer.getDate() + ";";
			openDataBase();
			db.execSQL(query);
		}
		catch (Exception e)
		{
			Utils.println("getSearchResults ERROR = " + e);
			updated = false;
		}
		finally
		{
			if (cursor != null) cursor.close();
			close();
		}
		return updated;
	}

	/**
	 * Deletes the prayer record from the current record table and also deletes
	 * the date from the current prayer date table
	 */
	public boolean deleteCurrentPrayer(DailyPrayer prayer)
	{
		return deleteCurrentPrayerRecord(prayer.getDate());
	}

	public boolean deleteCurrentPrayerRecord(long date)
	{
		boolean updated = true;
		try
		{
			String prayerRecordQuery = "delete from current_prayer_record where date = " + date + ";";
			String qazaDateQuery = "delete from current_prayer_dates where date = " + date + ";";
			openDataBase();
			db.execSQL(qazaDateQuery);
			db.execSQL(prayerRecordQuery);
		}
		catch (Exception e)
		{
			Utils.println("delete prayer ERROR = " + e);
			updated = false;
		}
		finally
		{
			close();
		}
		return updated;
	}

	/**
	 * Deletes the prayer record from the qaza prayer record table and also
	 * deletes the date from the qaza date table
	 */
	public boolean deleteQazaPrayerRecord(DailyPrayer prayer)
	{
		boolean updated = true;
		try
		{
			String prayerRecordQuery = "delete from prayer_record where date = " + prayer.getDate() + ";";
			String qazaDateQuery = "delete from qaza_dates where date = " + prayer.getDate() + ";";
			openDataBase();
			db.execSQL(qazaDateQuery);
			Utils.println("Executing Query: " + qazaDateQuery);
			db.execSQL(prayerRecordQuery);
			Utils.println("Executing Query: " + prayerRecordQuery);
		}
		catch (Exception e)
		{
			Utils.println("getSearchResults ERROR = " + e);
			updated = false;
		}
		finally
		{
			close();
		}
		return updated;
	}

	public DailyPrayer searchCurrentPrayerRecord(long longDate)
	{
		DailyPrayer dp = null;
		Cursor cursor = null;
		try
		{
			String query = "select * from current_prayer_record where date = " + longDate + ";";
			openDataBase();
			cursor = db.rawQuery(query, null); // TODO check if the
												// cursor
			// can be null?

			while (cursor.moveToNext())
			{
				dp = new DailyPrayer();
				dp.setDate(cursor.getLong(0));
				String value = cursor.getString(1);
				if ("TRUE".equalsIgnoreCase(value))
				{
					dp.setFajr(true);
				}
				value = cursor.getString(2);
				if ("TRUE".equalsIgnoreCase(value))
				{
					dp.setZuhr(true);
				}
				value = cursor.getString(3);
				if ("TRUE".equalsIgnoreCase(value))
				{
					dp.setAsar(true);
				}
				value = cursor.getString(4);
				if ("TRUE".equalsIgnoreCase(value))
				{
					dp.setMaghrib(true);
				}
				value = cursor.getString(5);
				if ("TRUE".equalsIgnoreCase(value))
				{
					dp.setIsha(true);
				}
			}
		}
		catch (Exception e)
		{
			Utils.println("getSearchResults ERROR = " + e);
		}
		finally
		{
			if (cursor != null) cursor.close();
			close();
		}
		return dp;
	}

	public DailyPrayer searchQazaPrayerRecord(long longDate)
	{
		DailyPrayer dp = null;
		Cursor cursor = null;
		try
		{
			String query = "select * from prayer_record where date = " + longDate + ";";
			openDataBase();
			cursor = db.rawQuery(query, null); // TODO check if the
												// cursor
			// can be null?

			while (cursor.moveToNext())
			{
				dp = new DailyPrayer();
				dp.setDate(cursor.getLong(0));
				String value = cursor.getString(1);
				if ("TRUE".equalsIgnoreCase(value))
				{
					dp.setFajr(true);
				}
				value = cursor.getString(2);
				if ("TRUE".equalsIgnoreCase(value))
				{
					dp.setZuhr(true);
				}
				value = cursor.getString(3);
				if ("TRUE".equalsIgnoreCase(value))
				{
					dp.setAsar(true);
				}
				value = cursor.getString(4);
				if ("TRUE".equalsIgnoreCase(value))
				{
					dp.setMaghrib(true);
				}
				value = cursor.getString(5);
				if ("TRUE".equalsIgnoreCase(value))
				{
					dp.setIsha(true);
				}
			}
		}
		catch (Exception e)
		{
			Utils.println("getSearchResults ERROR = " + e);
		}
		finally
		{
			if (cursor != null) cursor.close();
			close();
		}
		return dp;
	}

	public boolean deleteCurrentPrayerMonth(int month, int year)
	{
		boolean updated = true;
		Cursor cursor = null;
		try
		{
			String prayerRecordQuery = "delete from current_prayer_dates where date = " + month + " and year = " + year + ";";
			openDataBase();
			db.execSQL(prayerRecordQuery);
		}
		catch (Exception e)
		{
			Utils.println("Delete prayer month ERROR = " + e);
			updated = false;
		}
		finally
		{
			if (cursor != null) cursor.close();
			close();
		}
		return updated;
	}

	/**
	 * Check if the month is added to the current prayer dates table
	 * 
	 * @return true if the month is added otherwise false
	 */
	public boolean isMonthAdded(int month, int year)
	{
		boolean added = false;
		Cursor cursor = null;
		try
		{
			String query = "select * from current_prayer_dates where month = " + month + " and year = " + year + ";";
			openDataBase();
			cursor = db.rawQuery(query, null); // TODO check if the
												// cursor
			while (cursor.moveToNext())
			{
				added = true;
			}
		}
		catch (Exception e)
		{
			Utils.println("isMonthAdded ERROR = " + e);
		}
		finally
		{
			if (cursor != null) cursor.close();
			close();
		}
		return added;
	}
}